/*
 * @(#)ExcelReader.java 创建于 2014年5月4日 
 * 
 * Copyright (c) 2013-2014 by lzy.  
 *
 */
package com.hangxin.commons.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 
 * 类名称：ExcelReader.java
 * <P/>
 * 类描述： 无
 * <P/>
 * 创建时间：2014年5月4日
 * <P/>
 * 创建人： 李志远
 * <P/>
 * 联系方式： 1726021389@qq.com
 * <P/>
 * 修改人：无
 * <P/>
 * 修改时间：无
 * <P/>
 * 修改备注：无
 * <P/>
 * 版本：v1.0
 * 
 */

public class ExcelReader
{
	// 工作簿
	private HSSFWorkbook wb = null;// book [includes sheet]

	// 工作表
	private HSSFSheet sheet = null;

	// 行
	private HSSFRow row = null;

	// 第sheetnum个工作表
	private int sheetNum = 0;

	// 第rowNum行
	private int rowNum = 0;

	// 文件输入流
	private FileInputStream fis = null;

	private File file = null;



	public ExcelReader()
	{
	}



	public ExcelReader(File file)
	{
		this.file = file;
	}



	public void setRowNum(int rowNum)
	{
		this.rowNum = rowNum;
	}



	public void setSheetNum(int sheetNum)
	{
		this.sheetNum = sheetNum;
	}



	public void setFile(File file)
	{
		this.file = file;
	}



	/**
	 * 读取excel文件获得HSSFWorkbook对象
	 */
	public void open() throws IOException
	{
		fis = new FileInputStream(file);
		wb = new HSSFWorkbook(new POIFSFileSystem(fis));
		fis.close();
	}



	/**
	 * 返回sheet表数目
	 * 
	 * @return int
	 */
	public int getSheetCount()
	{
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}



	/**
	 * sheetNum下的记录行数
	 * 
	 * @return int
	 */
	public int getRowCount()
	{
		if (wb == null)
		{
			System.out.println("=============>WorkBook为空");
		}
		HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}



	/**
	 * 读取指定sheetNum的rowCount
	 * 
	 * @param sheetNum
	 * @return int
	 */
	public int getRowCount(int sheetNum)
	{
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}



	/**
	 * 得到指定行的内容
	 * 
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int lineNum)
	{
		return readExcelLine(this.sheetNum, lineNum);
	}



	/**
	 * 读取指定工作表和行数的内容
	 * 
	 * @param sheetNum
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int sheetNum, int lineNum)
	{
		if (sheetNum < 0 || lineNum < 0)
		{
			return null;
		}
		String[] strExcelLine = null;
		try
		{
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);
			if (null != row)
			{
				int cellCount = row.getLastCellNum();
				strExcelLine = new String[cellCount + 1];
				for (int i = 0; i <= cellCount; i++)
				{
					strExcelLine[i] = readStringExcelCell(lineNum, i);
				}
			}

		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return strExcelLine;
	}



	/**
	 * 读取指定列的内容
	 * 
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int cellNum)
	{
		return readStringExcelCell(this.rowNum, cellNum);
	}



	/**
	 * 指定行和列编号的内容
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int rowNum, int cellNum)
	{
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}



	/**
	 * 指定工作表、行、列下的内容
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	@SuppressWarnings("deprecation")
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum)
	{
		if (sheetNum < 0 || rowNum < 0)
		{
			return "";
		}
		String strExcelCell = "";
		try
		{
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell((short) cellNum) != null)
			{ // add this condition
				switch (row.getCell((short) cellNum).getCellType())
				{
					case HSSFCell.CELL_TYPE_FORMULA:// 公式
						strExcelCell = "FORMULA ";
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:// 数字
					{
						strExcelCell = String.valueOf(row.getCell((short) cellNum).getNumericCellValue());
					}
						break;
					case HSSFCell.CELL_TYPE_STRING:// 字符串
						strExcelCell = row.getCell((short) cellNum).getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BLANK:// 空值
						strExcelCell = "";
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:// boolean型值
						strExcelCell = String.valueOf(row.getCell((short) cellNum).getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_ERROR:
						strExcelCell = String.valueOf(row.getCell((short) cellNum).getErrorCellValue());
						break;
					default:
						strExcelCell = "";
						break;
				}
			}
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return strExcelCell;
	}



	public static void main(String args[])
	{
		File file = new File("C:\\qt.xls");
		// 通过得到的文件输入流inputstream创建一个HSSFWordbook对象
		ExcelReader readExcel = new ExcelReader(file);
		try
		{
			readExcel.open();
		} catch (IOException e)
		{
			e.printStackTrace();
		}
		System.out.println(readExcel.getSheetCount());
		for (int k = 0; k < readExcel.getSheetCount(); k++)
		{
			readExcel.setSheetNum(k); // 设置读取索引为0的工作表
			// 总行数
			int count = readExcel.getRowCount();
			for (int i = 0; i <= count; i++)
			{
				String[] rows = readExcel.readExcelLine(i);
				if (rows != null)
				{
					for (int j = 0; j < rows.length; j++)
					{
						System.out.print(rows[j] + " ");
					}
					System.out.print("\n");
				}
			}
		}
	}
}
